package com.gjintel.business.report.dao.Impl;

import com.gjintel.auction.business.model.AuctionInfo;
import com.gjintel.business.proj.model.DepositReturn;
import com.gjintel.business.proj.model.ProjectInfo;
import com.gjintel.business.report.dao.IAuctionReportDao;
import com.gjintel.framework.register.model.DepositCategory;
import com.gjintel.sdk.base.dao.HibernateGenericDAO;
import com.gjintel.sdk.base.dao.Page;
import com.gjintel.sdk.base.dao.Sort;
import com.gjintel.sdk.util.StringUtil;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

@Repository("auctionReportDao")
public class AuctionReportDaoImpl extends HibernateGenericDAO implements IAuctionReportDao {

	@Override
	public List<DepositCategory> listAuction() {
		String sql ="select count(*) as count,t.name as name from `t_sys_register_deposit` d,`t_base_project_type` t WHERE d.deposit_categoryId=t.id GROUP BY d.deposit_categoryId ";
		List<DepositCategory> list=this.findBySQL(DepositCategory.class, sql);
		return list;
	}
	

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositReturn> getAuctionDepositList(String projectName, String code, Page page, Integer depositType, String projectType) {
		List<Object> params = new ArrayList<Object>();
	    StringBuffer hql= new StringBuffer("from DepositReturn dr where 1 = 1 ");
		if(projectName!= null && !projectName.equals("")){
			hql.append("and dr.projectName like ?" );
			params.add("%" +projectName+ "%");
		} if(code!= null && !code.equals("")){
			hql.append("and dr.projectCode like ?");
			params.add("%" +code+ "%");
		}if(depositType !=null){
			hql.append("and dr.pk.depositType= ?");
			params.add(depositType);
		}if(projectType !=null && !projectType.equals("") ){
			hql.append("and dr.projectType like ?");
			params.add("%" +projectType+ "%");
		}
		return this.findAsPage(hql.toString(), page, params.toArray());
	}

	@Override
	public List<AuctionInfo> findAuctionReportByProjectCase(AuctionInfo auction, Page page, Sort sort, Date startTime,
                                                            Date stopTime) {
		List<Object> paramList=new ArrayList<>();
		//StringBuffer hql=new StringBuffer("from ProjectInfo auction where auction.tradeTime!=null ");//查询结项、流标、撤销的项目
		StringBuffer sql=new StringBuffer();
		sql.append("select a.id as id,a.auction_name as auctionName,a.catagory_name as catagoryName,a.owner_name as ownerName,a.code as code "
			+" ,a.create_time as createTime,a.auction_show_start as auctionShowStart,a.hit_show_start as hitShowStart"
			//中标单位
			+",	a.winner_name as winnerName "
			//中标金额
			+",	a.hit_price as hitPrice "
			+" ,a.trade_time as tradeTime,a.status as status "
			+" from `t_auction_info` a where a.trade_time is not null ");
		
		//业务数据条件q
		if(auction !=null){
			if (!StringUtil.isEmpty(auction.getAuctionName())) {//名称
				sql.append(" and a.auction_name like ?");
				paramList.add("%"+ auction.getAuctionName()+"%");
			}
			if (!StringUtil.isEmpty(auction.getCode())) {//编号
				sql.append(" and a.code like ?");
				paramList.add("%"+ auction.getCode()+"%");
			}
			if (!StringUtil.isEmpty(auction.getCatagoryId())) {//类型
				sql.append(" and a.catagory_id = ?");
				paramList.add(auction.getCatagoryId());
			}
			if (!StringUtil.isEmpty(auction.getFundFrom())) {//资金性质
				sql.append(" and a.fund_Type = ?");
				paramList.add(auction.getFundFrom());
			}
		}

		//交易时间段
		if(startTime != null){
			sql.append(" and a.trade_time >= ? ");
			paramList.add(startTime);
		}
		if(stopTime != null){
			sql.append(" and a.trade_time <= ? ");
			paramList.add(stopTime);
		}
		
		sql.append(" order by a.create_time desc");
		return this.findPageByComSql(AuctionInfo.class, sql.toString(), page, paramList.toArray());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositReturn> getAuctionDepositListExport(String projectName, String code, Integer depositType, String projectType) {
		List<Object> params = new ArrayList<Object>();
	    StringBuffer hql= new StringBuffer("from DepositReturn dr where 1 = 1 ");
		if(projectName!= null && !projectName.equals("")){
			hql.append("and dr.projectName like ?");
			params.add("%" +projectName+ "%");
		} if(code!= null && !code.equals("")){
			hql.append("and dr.projectCode like ?");
			params.add("%" +code+ "%");
		} if(depositType !=null){
			hql.append("and dr.pk.depositType= ?");
			params.add(depositType);
		}if(projectType !=null && !projectType.equals("")){
			hql.append("and dr.projectType like ?");
			params.add("%" +projectType+ "%");
		}
		return this.find(hql.toString(), params.toArray());
	}

	@Override
	public BigDecimal findAuctionDepositAmountByDate(Integer depositType,Integer status, Date startTime, Date stopTime,String className) {
		List<Object> params=new ArrayList<>();
		StringBuffer hql=new StringBuffer("select SUM(d.tnAmount) from "+className+" d where d.pk.depositType=? ");
		params.add(depositType);
		
		//时间段(收入)--入账时间
		if(startTime != null&&status==null){
			hql.append(" and d.transTime >= ? ");
			params.add(startTime);
		}
		if(stopTime != null&&status==null){
			hql.append(" and d.transTime <= ? ");
			params.add(stopTime);
		}
		
		//时间段(支出)--退款时间
		if(startTime != null&&status!=null){
			hql.append(" and d.returnTime >= ? ");
			params.add(startTime);
		}
		if(stopTime != null&&status!=null){
			hql.append(" and d.returnTime <= ? ");
			params.add(stopTime);
		}
				
		
		if(status!=null){
			hql.append(" and d.status=?");
			params.add(status);
		}
		return (BigDecimal) this.findUnique(hql.toString(), params.toArray());
	}


	@Override
	public List findAuctionDepositListByDate(Page page, Integer depositType, Integer status, Date startTime, Date stopTime, String className) {
		List<Object> params=new ArrayList<>();
		StringBuffer hql=new StringBuffer("from "+className+" d where 1=1 ");
		//时间段(收入)--入账时间
		if(startTime != null&&status==null){
			hql.append(" and d.transTime >= ? ");
			params.add(startTime);
		}
		if(stopTime != null&&status==null){
			hql.append(" and d.transTime <= ? ");
			params.add(stopTime);
		}
		
		//时间段(支出)--退款时间
		if(startTime != null&&status!=null){
			hql.append(" and d.returnTime >= ? ");
			params.add(startTime);
		}
		if(stopTime != null&&status!=null){
			hql.append(" and d.returnTime <= ? ");
			params.add(stopTime);
		}
		if(status!=null){
			hql.append(" and d.status=?");
			params.add(status);
		}
		if(depositType!=null){//保证金类型
			hql.append(" and d.pk.depositType=?");
			params.add(depositType);
		}
		return this.findAsPage(hql.toString(), page, params.toArray());
	}


	@Override
	public List findAuctionReportByProjectList(ProjectInfo project, Page page, Sort sort, Date startTime, Date stopTime) {
		List<Object> paramList=new ArrayList<>();
		StringBuffer hql=new StringBuffer("from ProjectInfo auction where auction.bidStartTime!=null ");//查询有开标时间的
		
		//开标时间段
		if(startTime != null){
			hql.append(" and auction.bidStartTime >= ? ");
			paramList.add(startTime);
		}
		if(stopTime != null){
			hql.append(" and auction.bidStartTime <= ? ");
			paramList.add(stopTime);
		}
		
		hql.append(" order by auction.bidStartTime desc");
		return this.findAsPage(hql.toString(), page, sort, "auction", false, paramList.toArray());
	}
	
	@Override
	public List findAuctionReportByNoOpentBidProjectList(ProjectInfo project, Page page, Sort sort, Date startTime, Date stopTime) {
		List<Object> paramList=new ArrayList<>();
		StringBuffer hql=new StringBuffer("from ProjectInfo auction where auction.bidStartTime!=null and auction.status in (7) ");//查询有开标时间的,并且未开标
		
		//开标时间段
		if(startTime != null){
			hql.append(" and auction.bidStartTime >= ? ");
			paramList.add(startTime);
		}
		if(stopTime != null){
			hql.append(" and auction.bidStartTime <= ? ");
			paramList.add(stopTime);
		}
		if(project != null){
			hql.append(" and auction.auditable = ? ");
			paramList.add(project.getAuditable());
		}

		hql.append(" order by auction.bidStartTime desc");
		return this.findAsPage(hql.toString(), page, sort, "auction", false, paramList.toArray());
	}


}
